<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
  <title>SETUP</title>
	<meta http-equiv="content-type" content="text/html; charset=utf-8" />
    <link rel="stylesheet" media="screen" type="text/css" title="Design" href="styles/design.css" />
</head>

<body>
    <!-- TITLE  -->
	<div id="page_title">
      <h1>System Setup</h1>
    </div>

    <!-- Side Menues -->
    <?php include "tools/Menues.html"; ?>

    <!-- Main Body -->
    <div id="page_body">
<?php
include "tools/Forms.php";
include "tools/Queries.php";

function ShowBackButton() {
    echo "\n<form action='setup.php?m=back' method='post'>";
    echo "\n<input type='submit' name='back' value='Back'></form>";
}

/************************************************************
 * Setup Page in mode CREATE
 ************************************************************/
if($_GET['m']=='create') {
    include 'tools/dbinfo.inc.php';

    /* Open connection: */
    $con = mysql_connect($hostname, $username, $password);
    if (!$con) {
        die('Error - Could not connect: '.mysql_error()).'<br />';
    }

    /* Create database: */
    if (mysql_query("CREATE DATABASE ".$database,$con)) {
        echo "Database successfully created. <br />";
    }
    else {
        echo "Error - Couldn't create database: ".mysql_error()."<br /> ";
    }

    @mysql_select_db($database, $con) or die("Couldn't select database:".mysql_error()."<br />");

    function CreateTable() {
        global $sqlcmd, $tablename, $con;
        if(mysql_query($sqlcmd, $con)) {
            echo "Successfully created ".$tablename." table. <br />"; }
        else {
            echo "SQL error when creating ".$tablename.":".mysql_error()." <br />";}
    }

    $tablename= "client";
    $sqlcmd= "CREATE TABLE clients
    (
    clientID  int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(clientID),
    FirstName varchar(20),
    LastName  varchar(20),
    VATnum    varchar(15),
    Address   varchar(50),
    PostCode  int,
    Town      varchar(15),
    Country   varchar(20),
    DateAdded date NOT NULL
    )";
    CreateTable();

    $tablename= "suppliers";
    $sqlcmd= "CREATE TABLE suppliers
    (
    supplierID int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(supplierID),
    Name      varchar(30),
    VATnum    varchar(15),
    Address   varchar(50),
    PostCode  int,
    Town      varchar(15),
    Country   varchar(20),
    DateAdded date NOT NULL,
    BankRupt  int(1)
    )";
    CreateTable();

    // @note: itemID is the item name
    $tablename= "items";
    $sqlcmd= "CREATE TABLE items
    (
    itemID    varchar(30) NOT NULL,
    supplierID int NOT NULL,
    PRIMARY KEY(itemID),
    FOREIGN KEY(supplierID) references suppliers(supplierID),
    Brand     varchar(15),
    Descrip   text(100),
    Capacity  int,
    BarCode   varchar(50),
    SellCost  double,
    BuyCost   double,
    VATrate   double,
    Image     text(300),
    Active    boolean
    )";
    CreateTable();

    // @note: facilityID is it's name
    $tablename= "facilities";
    $sqlcmd= "CREATE TABLE facilities
    (
    facilityID varchar(30) NOT NULL,
    PRIMARY KEY(facilityID),
    Address   varchar(50),
    PostCode  int,
    Town      varchar(15),
    Country   varchar(20)
    )";
    CreateTable();

    $tablename= "sales";
    $sqlcmd="CREATE TABLE sales
    (
    salesID int NOT NULL AUTO_INCREMENT,
    clientID int NOT NULL,
    facilityID varchar(30) NOT NULL,
    PRIMARY KEY(salesID),
    FOREIGN KEY(clientID) references clients(clientID),
    FOREIGN KEY(facilityID) references facilities(facilityID),
    datetime  datetime
    )";
    CreateTable();

    $tablename="purchases";
    $sqlcmd="CREATE TABLE purchases
    (
    purchaseID int NOT NULL AUTO_INCREMENT,
    supplierID int NOT NULL,
    facilityID varchar(30) NOT NULL,
    PRIMARY KEY(purchaseID),
    FOREIGN KEY(supplierID) references suppliers(supplierID),
    FOREIGN KEY(facilityID) references facilities(facilityID),
    datetime  datetime
    )";
    CreateTable();

    $tablename= "itemStock";
    $sqlcmd= "CREATE TABLE itemStock
    (
    stockID int NOT NULL AUTO_INCREMENT,
    itemID varchar(30) NOT NULL,
    facilityID varchar(30) NOT NULL,
    PRIMARY KEY(stockID),
    FOREIGN KEY(itemID) references items(itemID),
    FOREIGN KEY(facilityID) references facilities(facilityID),
    Amount int
    )";
    CreateTable();

    $tablename= "itemsPurchased";
    $sqlcmd= "CREATE TABLE itemsPurchased
    (
    ipurchaseID int NOT NULL AUTO_INCREMENT,
    purchaseID int NOT NULL,
    itemID varchar(30) NOT NULL,
    PRIMARY KEY(ipurchaseID),
    FOREIGN KEY(purchaseID) references purchases(purchaseID),
    FOREIGN KEY(itemID) references items(itemID),
    qty int,
    cost double
    )";
    CreateTable();

    $tablename= "itemsSold";
    $sqlcmd= "CREATE TABLE itemsSold
    (
    isalesID int NOT NULL AUTO_INCREMENT,
    salesID int NOT NULL,
    itemID varchar(30) NOT NULL,
    PRIMARY KEY(isalesID),
    FOREIGN KEY(salesID) references sales(salesID),
    FOREIGN KEY(itemID) references items(itemID),
    qty int,
    cost double
    )";
    CreateTable();
    
    mysql_close($con);
    ShowBackButton();
 }
/************************************************************
 * Setup Page in mode IMPORT
 ************************************************************/
 else if($_GET['m']=='import') {
     function loadcsv($filename, $table) {
         // Select the table :
         include 'tools/connect.php';
         echo "\n<hr color=#000077 width=95% size=3 />";
         echo "\n<h2><font color=#000077>".$table.":</font></h2>";
         $sql = "SELECT * FROM ".$table." LIMIT 0,1";

         $response=mysql_query($sql);
         if(!$response) {
             echo '\nError getting columns: '.mysql_error().'<br/>';
             mysql_close();
             return;
         }

         // Get column names :
         $i = 0;
         $columns = "(";
         while ($i < mysql_num_fields($response)) {
             $meta = mysql_fetch_field($response, $i);
             if($i==0) {$columns = $columns."" .$meta->name;}
             else      {$columns = $columns.",".$meta->name;}
             $i++;
         }
         $columns=$columns.")";

         // Get Delimiter and New Line seperators.
         if($_POST['dlm']!='') {
             $dlm = str_replace("\\t","\t",$_POST['dlm']);
         }
         else {$dlm = ';';}
         if($_POST['nlc']!='') {
             $nlc = str_replace("\\r","\r",$_POST['nlc']);
             $nlc = str_replace("\\n","\n",$_POST['nlc']);
         }
         else {$nlc = '\r';}

         // Load the CSV file:
         // TODO: Fix this blasted new-line problem! >:O
         $sql = "
             LOAD DATA LOCAL INFILE '$filename' INTO TABLE $table
             FIELDS TERMINATED by '$dlm'
              ENCLOSED by '\"'
             LINES TERMINATED by '$nlc'
             $columns";
         $check = mysql_query($sql);
         if (!$check) {
             echo "\n".mysql_error()."<br/>";
             mysql_close();
             return;
         }

         // Display Results :
         echo "Import of table '".$table."' done.<br/>";         
         echo "\n<h4><font color=blue>Preview:</font></h4>";
         $sql = "SELECT * FROM ".$table." LIMIT 0 , 5";
         $response = mysql_query($sql);
         EchoTable($response);

         mysql_close();
     }
     function importTable($table) {
         if(!isset($_POST[$table])) {
             echo "<font color=red><b>File name for table '".$table."' missing.";
             echo "Skipped.</b></font><br/>";
         }
         else {
             loadcsv($_POST[$table], $table);
         }
     }

     importTable("clients");
     importTable("suppliers");
     importTable("items");
     importTable("sales");
     importTable("itemsSold");
     importTable("purchases");
     importTable("itemsPurchased");
     importTable("facilities");
     importTable("itemStock");
     
     ShowBackButton();
 }
/************************************************************
 * Setup Page in mode WELCOME
 ************************************************************/
 else {
     echo "\n<h3><font color=blue> Welcome to the Toupropre DBMS Setup Page</font></h3>";
     echo "\n<p class=text> This page will guide you through getting the DMBS up and ";
     echo "running as soon as possible."; 
     echo "If this is the first time you see this screen or you think";
     echo "that the system isn't correctly initialized, click Initialize.</p>";
     echo "\n<form action='setup.php?m=create' method='post'>";
     echo "\n<input type='submit' name='create' value='Initialize'></form>";

    echo "\n<h4><font color=blue>File import</font></h4>";
    echo "\nEnter a file name in the text area and click Import to load the data.<br/><br/>";
    echo "\nFiles much be temporarly uploaded to the server for importing. They may be ";
    echo "\nremoved after the setup is complete.<br/><br/>";

    EchoCSVForm('setup.php?m=import', "submit","Import");
    echo "<p class='text'>Defaults: ';' delimiter, '\\r' new line seperator</p>";
 }

?>

    </div>
    
    <!-- Footer -->
    <?php include "tools/Footer.html"; ?>
</body>
</html>